<?php

function dbconnect() 
	{ 
		require('../config.php'); 
		static $connect = null; 
		if ($connect === null) 
			{
				 $connect =  mysql_connect($host, $user, $passwd) or die(mysql_error());
				 mysql_query("SET character_set_results=utf8", $connect);
				 mysql_select_db($database); 
			} 
		return $connect; 
	} 
	
function getbyall_hoadon_thang()
	{ 
		$allnxb_req = mysql_query("SELECT Hoa_don.*, Nguoi_dung.Ho_va_ten 
		FROM hoa_don 
		INNER JOIN Nguoi_dung 
		ON Nguoi_dung.ID = Hoa_don.ID_Nguoi_dung 
		WHERE (ID_Tinh_trang_hoa_don=3 and MONTH(Thoi_gian)= MONTH(NOW()) )
		order by ID DESC" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	}

function getbyall_hoadon_thoigian($bd, $kt)
	{ 
		$allnxb_req = mysql_query("SELECT Hoa_don.*, Nguoi_dung.Ho_va_ten 
		FROM hoa_don 
		INNER JOIN Nguoi_dung 
		ON Nguoi_dung.ID = Hoa_don.ID_Nguoi_dung 
		WHERE (ID_Tinh_trang_hoa_don=3 and Thoi_gian >= N'".$bd."' and Thoi_gian < N'".$kt."'  )
		order by ID DESC" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	}
function getbyall_hoadon_thang_truoc()
	{ 
		$allnxb_req = mysql_query("SELECT Hoa_don.*, Nguoi_dung.Ho_va_ten 
		FROM hoa_don 
		INNER JOIN Nguoi_dung 
		ON Nguoi_dung.ID = Hoa_don.ID_Nguoi_dung 
		WHERE (ID_Tinh_trang_hoa_don=3 and MONTH(Thoi_gian)= (MONTH(NOW())-1) )
		order by ID DESC" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	} 

function getsach_banchay_thang()
	{ 
		$allnxb_req = mysql_query("SELECT chi_tiet_hoa_don.*,sach.Ten_sach, SUM(Chi_tiet_hoa_don.So_luong) as tongsoluong 
		FROM `chi_tiet_hoa_don` 
		INNER JOIN Hoa_don 
		ON Chi_tiet_hoa_don.ID_Hoa_don = Hoa_don.ID 
		INNER JOIN Sach 
		On Chi_tiet_hoa_don.ID_Sach= Sach.ID 
		where (Hoa_don.ID_Tinh_trang_hoa_don=3 and month(Hoa_don.Thoi_gian)=month(now()))
		group by Chi_tiet_hoa_don.ID_Sach 
		order by tongsoluong desc, Chi_tiet_hoa_don.ID DESC" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	} 
	
function getsach_banchay_thang_truoc()
	{ 
		$allnxb_req = mysql_query("SELECT chi_tiet_hoa_don.*,sach.Ten_sach, SUM(Chi_tiet_hoa_don.So_luong) as tongsoluong 
		FROM `chi_tiet_hoa_don` 
		INNER JOIN Hoa_don 
		ON Chi_tiet_hoa_don.ID_Hoa_don = Hoa_don.ID 
		INNER JOIN Sach 
		On Chi_tiet_hoa_don.ID_Sach= Sach.ID 
		where (Hoa_don.ID_Tinh_trang_hoa_don=3 and month(Hoa_don.Thoi_gian)= (month(now())-1))
		group by Chi_tiet_hoa_don.ID_Sach 
		order by tongsoluong desc, Chi_tiet_hoa_don.ID DESC" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	}
function getbynhap_thang()
	{ 
		$allnxb_req = mysql_query("SELECT Hoa_don_nhap.*, Nha_cung_cap.Ten_nha_cung_cap
		FROM hoa_don_nhap 
		INNER JOIN Nha_cung_cap 
		ON hoa_don_nhap.ID_Nha_cung_cap = Nha_cung_cap.ID
		WHERE (MONTH(Thoi_gian)= MONTH(NOW()) )
		order by ID DESC" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	} 
function getbynhap_thoigian($bd,$kt )
	{ 
		$allnxb_req = mysql_query("SELECT Hoa_don_nhap.*, Nha_cung_cap.Ten_nha_cung_cap
		FROM hoa_don_nhap 
		INNER JOIN Nha_cung_cap 
		ON hoa_don_nhap.ID_Nha_cung_cap = Nha_cung_cap.ID
		WHERE ( Thoi_gian >= N'".$bd."' and Thoi_gian < N'".$kt."')
		order by ID DESC" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	} 
function getbynhap_thang_truoc()
	{ 
		$allnxb_req = mysql_query("SELECT Hoa_don_nhap.*, Nha_cung_cap.Ten_nha_cung_cap
		FROM hoa_don_nhap 
		INNER JOIN Nha_cung_cap 
		ON hoa_don_nhap.ID_Nha_cung_cap = Nha_cung_cap.ID
		WHERE (MONTH(Thoi_gian)= (MONTH(NOW())-1) )
		order by ID DESC" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	} 
function getbyall_sachhh()
	{ 
		$allnxb_req = mysql_query("SELECT Sach.*, Chu_de.Ten_chu_de, Tinh_trang_sach.Ten_tinh_trang
					FROM (Sach INNER JOIN Chu_de
					ON Sach.ID_Chu_de=Chu_de.ID) 
					INNER JOIN Tinh_trang_sach
					ON Sach.ID_Tinh_trang_sach=Tinh_trang_sach.ID
					WHERE (So_luong<=0 and ID_Tinh_trang_sach !=3 and ID_Tinh_trang_sach !=2)
					order by ID DESC" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	} 	
	
function getbyall_sachsaphh()
	{ 
		$allnxb_req = mysql_query("SELECT Sach.*, Chu_de.Ten_chu_de, Tinh_trang_sach.Ten_tinh_trang
					FROM (Sach INNER JOIN Chu_de
					ON Sach.ID_Chu_de=Chu_de.ID) 
					INNER JOIN Tinh_trang_sach
					ON Sach.ID_Tinh_trang_sach=Tinh_trang_sach.ID
					WHERE (So_luong<=10 and So_luong>0 and ID_Tinh_trang_sach !=3 and ID_Tinh_trang_sach !=2)
					order by So_luong DESC, ID DESC" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	} 	
function getbyid_ctx($id)
	{
		$allnxb_req= mysql_query("SELECT * FROM Chi_tiet_hoa_don WHERE ID_Hoa_don=N'".$id."'",dbconnect());
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result;  
	}
function getbyid_ctn($id)
	{
		$allnxb_req= mysql_query("SELECT * FROM Chi_tiet_nhap WHERE ID_Hoa_don_nhap=N'".$id."'",dbconnect());
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result;  
	}
	
	
function getsach_banchay_thoigian($bd,$kt)
	{ 
		$allnxb_req = mysql_query("SELECT chi_tiet_hoa_don.*,sach.Ten_sach, SUM(Chi_tiet_hoa_don.So_luong) as tongsoluong 
		FROM `chi_tiet_hoa_don` 
		INNER JOIN Hoa_don 
		ON Chi_tiet_hoa_don.ID_Hoa_don = Hoa_don.ID 
		INNER JOIN Sach 
		On Chi_tiet_hoa_don.ID_Sach= Sach.ID 
		where (Hoa_don.ID_Tinh_trang_hoa_don=3 and Hoa_don.Thoi_gian >= N'".$bd."' and Hoa_don.Thoi_gian < N'".$kt."')
		group by Chi_tiet_hoa_don.ID_Sach 
		order by tongsoluong desc, Chi_tiet_hoa_don.ID DESC
		limit 20" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	} 
	
	function getby10_kh_giatri_nhieunhat($bd,$kt)
	{ 
		$allnxb_req = mysql_query("SELECT ID_Nguoi_dung, SUM(hoa_don.Tong_tien) as tt, SUM(hoa_don.Tong_lai) as tl, Nguoi_dung.Ho_va_ten 
		FROM hoa_don 
		INNER JOIN Nguoi_dung 
		ON Nguoi_dung.ID = Hoa_don.ID_Nguoi_dung 
		WHERE (ID_Tinh_trang_hoa_don=3 and Thoi_gian >= N'".$bd."' and Thoi_gian < N'".$kt."')
		GROUP BY ID_Nguoi_dung
		order by tt DESC
		limit 10" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	} 
function getbyid_nguoidung($id, $bd, $kt)
	{ 
		$allnxb_req = mysql_query("SELECT *
		FROM hoa_don 
		WHERE (ID_Tinh_trang_hoa_don=3 and ID_Nguoi_dung=N'".$id."' and Thoi_gian >= N'".$bd."' and Thoi_gian < N'".$kt."' )
		order by ID DESC" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	} 
	
function getsach_tonkho()
	{ 
		$allnxb_req = mysql_query("SELECT * FROM Sach
		
		order by So_luong desc
		limit 20" ,dbconnect()); 
		$result = array(); 
		while ($nxb = mysql_fetch_array($allnxb_req))
		 { 
			$result[] = $nxb; 
		 } 
		return $result; 
	} 	
function getbyid_giaban($id)
	{
		$result= mysql_query("SELECT * FROM gia_ban WHERE (ID_Sach='".$id."' and Thoi_gian<now()) order by Thoi_gian DESC limit 1",dbconnect());
		return mysql_fetch_array($result); 
	}
	function getbyid_gianhap($id)
	{
		$result= mysql_query("SELECT * FROM Chi_tiet_nhap WHERE (ID_Sach='".$id."' ) order by ID DESC limit 1",dbconnect());
		return mysql_fetch_array($result); 
	}
?>